﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace MTQ.DAL
{
    public class SqlHelperPage
    {
        /// <summary>
        /// sql.Append("(select row_number() over (order by " + _orderString + ") as rowId," + field + "from " + tableName);//查询语句
        /// </summary>
        /// <param name="sqlconnect"></param>
        /// <param name="field"></param>
        /// <param name="tableName"></param>
        /// <param name="_pagerIndex"></param>
        /// <param name="_pagerSize"></param>
        /// <param name="WhereString"></param>
        /// <param name="_orderString"></param>
        /// <returns></returns>
        public static DataSet GetDataPage(string sqlconnect, string field, string tableName, int _pagerIndex, int _pagerSize, string WhereString, string _orderString, params SqlParameter[] commandParameters)
        {
            string sql = GetPageString(field, tableName, _pagerIndex, _pagerSize, WhereString, _orderString);
            DataSet ds = SqlHelper.ExecuteDataset(sqlconnect, CommandType.Text, sql, commandParameters);
            return ds;
        }

        public static DataSet GetDataNoPage(string sqlconnect, string field, string tableName, string WhereString, string _orderString, params SqlParameter[] commandParameters)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(" select  " + field + "  from " + tableName);//查询语句
            if (!string.IsNullOrEmpty(WhereString))
            {
                sql.Append(" where " + WhereString + "  ");
            }

            _orderString = SpecialCode(_orderString);
            _orderString = RemoveKeyword(_orderString);
            if (string.IsNullOrEmpty(_orderString))//排序不能为空
            {
                _orderString = " id desc";
            }

            sql.Append(" order by " + _orderString);
            DataSet ds = SqlHelper.ExecuteDataset(sqlconnect, CommandType.Text, sql.ToString(), commandParameters);
            return ds;
        }


        /// <summary>
        /// 生成分页语句
        /// </summary>
        /// <param name="tableName">数据表名</param>
        /// <param name="whereString">Where后面的条件</param>
        /// <returns></returns>
        public static String GetPageString(string field, string tableName, int _pagerIndex, int _pagerSize, string WhereString, string _orderString)
        {
            StringBuilder sql = new StringBuilder();
            _orderString = SpecialCode(_orderString);
            _orderString = RemoveKeyword(_orderString);
            if (string.IsNullOrEmpty(_orderString))//排序不能为空
            {
                _orderString = " id desc";
            }


            sql.Append("(select row_number() over (order by " + _orderString + ") as rowId," + field + " from " + tableName);//查询语句
            if (!string.IsNullOrEmpty(WhereString))
            {
                sql.Append(" where " + WhereString);
            }
            if (_pagerIndex > 0)
            {
                _pagerIndex = _pagerIndex - 1;
            }
            int StartRecord = (_pagerIndex) * _pagerSize + 1;
            int EndRecord = StartRecord + _pagerSize - 1;
            string dd = sql.ToString();
            sql.Clear();
            sql.Append(" select * from " + dd + ") as t where rowId between " + StartRecord + " and " + EndRecord);
            //if (!string.IsNullOrEmpty(_orderString))
            //{
            // sql.Append(" ORDER BY " + _orderString);
            //}

            sql.Append(";  ");
            sql.Append(" select  count(*) as Number from " + tableName);//查询语句
            if (!string.IsNullOrEmpty(WhereString))
            {
                sql.Append(" where " + WhereString);
            }

            return sql.ToString();
        }

        #region 过滤特殊字符
        public static string SpecialCode(string s)
        {
            s = s.Replace(@"\", "");
            s = Regex.Replace(s, "[\\[\\]\\^\\-*×――(^)$%~!/@#$…&%￥—+=<>《》|!！??？:：•`·、。，；,.;\"‘’“”-]", "").ToUpper();
            return s;
        }

        public static string RemoveKeyword(string str)
        {
            //删除与数据库相关的词
            str = Regex.Replace(str, "select ", "", RegexOptions.IgnoreCase);
            str = Regex.Replace(str, "insert ", "", RegexOptions.IgnoreCase);
            str = Regex.Replace(str, "delete from", "", RegexOptions.IgnoreCase);
            str = Regex.Replace(str, "drop table ", "", RegexOptions.IgnoreCase);
            str = Regex.Replace(str, " truncate ", "", RegexOptions.IgnoreCase);
            str = Regex.Replace(str, " mid ", "", RegexOptions.IgnoreCase);
            str = Regex.Replace(str, " char ", "", RegexOptions.IgnoreCase);
            str = Regex.Replace(str, " xp_cmdshell", "", RegexOptions.IgnoreCase);
            str = Regex.Replace(str, " exec master", "", RegexOptions.IgnoreCase);
            str = Regex.Replace(str, " net localgroup administrators", "", RegexOptions.IgnoreCase);
            str = Regex.Replace(str, " and ", "", RegexOptions.IgnoreCase);
            str = Regex.Replace(str, " net user", "", RegexOptions.IgnoreCase);
            str = Regex.Replace(str, " net", "", RegexOptions.IgnoreCase);
            //Htmlstring = Regex.Replace(Htmlstring, "*", "", RegexOptions.IgnoreCase);
            str = Regex.Replace(str, "-", "", RegexOptions.IgnoreCase);
            str = Regex.Replace(str, " delete ", "", RegexOptions.IgnoreCase);
            str = Regex.Replace(str, " drop ", "", RegexOptions.IgnoreCase);
            str = Regex.Replace(str, " script ", "", RegexOptions.IgnoreCase);

            return str;

        }
        #endregion
    }
}
